﻿using System.Data;
using System.Data.SqlClient;
using System;

namespace Adams.Web.Areas.MyAdams.Data.Businesses
{
    public static class Index
    {
        public static MyAdams.Models.Businesses.Index.PageLoad PageLoad(int memberId)
        {
            var data = new MyAdams.Models.Businesses.Index.PageLoad()
            {
                Businesses = new Shared.Entities.Businesses.BusinessCollection()
            };

            using (SqlDataReader dr = Adams.Web.Data.DB.RunDataReader(@"
SELECT P.BusinessID,P.CategoryID,P.MemberID,P.StatusID,P.Name,P.Street,P.Street2,P.City,P.State,P.Zip,P.Phone,P.Description,P.CreatedOn,P.ExpiresOn,M.FirstName,M.LastName,M.Email,C.Name As CategoryName, T.TypeID AS TypeID, T.Name AS TypeName
FROM Businesses.Businesses P 
INNER JOIN Businesses.Categories C ON P.CategoryID = C.CategoryID 
INNER JOIN Businesses.Types T ON C.TypeID = T.TypeID 
INNER JOIN Membership.Members M ON P.MemberID = M.MemberID
WHERE P.MemberID = @MemberID 
AND P.StatusID <> 4 AND DATEDIFF(d, GETDATE(), P.ExpiresOn) >= -7
ORDER BY ExpiresOn ASC
", CommandType.Text,
    Adams.Web.Data.DB.CreateParameter("@MemberID", memberId)))
            {
                while (dr.Read())
                {
                    var business = Adams.Web.Data.DB.InitFromReader<Shared.Entities.Businesses.Business>(dr, new Adams.Web.Data.ColMap { NameInDb = "BusinessID", NameInObject = "ID" });
                    business.Status = (Shared.Entities.Businesses.Status)dr["StatusID"].DbInt();
                    business.Category = new Shared.Entities.Businesses.Category()
                    {
                        ID = dr["CategoryID"].DbInt(),
                        Name = dr["TypeName"].DbString(),
                        Type = new Shared.Entities.Businesses.Type()
                        {
                            ID = dr["TypeID"].DbInt(),
                            Name = dr["TypeName"].DbString()
                        }
                    };

                    business.Member = new Shared.Entities.Membership.Member()
                    {
                        ID = dr["MemberID"].DbInt(),
                        Email = dr["Email"].DbString(),
                        FirstName = dr["FirstName"].DbString(),
                        LastName = dr["LastName"].DbString(),
                    };

                    data.Businesses.Add(business);
                }
            }

            return data;
        }

        public static void Renew(int memberId, int businessId, DateTime expiresOn)
        {
            Adams.Web.Data.DB.Run(@"
UPDATE Businesses.Businesses
SET ExpiresOn = @ExpiresOn
WHERE BusinessID = @BusinessID AND MemberID = @MemberID
", CommandType.Text,
                    Adams.Web.Data.DB.CreateParameter("@BusinessID", businessId, SqlDbType.Int),
                    Adams.Web.Data.DB.CreateParameter("@MemberID", memberId, SqlDbType.Int),
                    Adams.Web.Data.DB.CreateParameter("@ExpiresOn", expiresOn));
        }

        public static void Remove(int memberId, int businessId)
        {
            Adams.Web.Data.DB.Run(@"
UPDATE Businesses.Businesses
SET StatusID = 4
WHERE BusinessID = @BusinessID AND MemberID = @MemberID
", CommandType.Text,
                    Adams.Web.Data.DB.CreateParameter("@BusinessID", businessId, SqlDbType.Int),
                    Adams.Web.Data.DB.CreateParameter("@MemberID", memberId, SqlDbType.Int));
        }
    }
}
